﻿using System;
using System.Data.SQLite;
using System.Collections;
using System.Collections.Generic;
using System.Data;

public abstract class BaseModel
{
    public const string CONNECTION_STRING = @"data source=../../database/userDB.db";
    protected string[] m_fieldList;
    protected string[] m_fieldType;
    protected string m_tableName;

    protected string m_sql;
    protected Hashtable m_paramList = new Hashtable();
    protected SQLiteConnection conn;

    public BaseModel()
    {
        if (this.conn == null)
        {
            this.conn = new SQLiteConnection(CONNECTION_STRING);
        }
    }

    #region Quick SQL
    public BaseModel sql(string sql)
    {
        m_sql = sql;
        return this;
    }

    public BaseModel addParam(object key, object data)
    {
        m_paramList.Add(key, data);
        return this;
    }

    public object[] singlequery()
    {
        object[] result = executeSingleRowQuery(m_sql, m_paramList);
        m_sql = "";
        m_paramList.Clear();
        return result;
    }

    public void execute()
    {
        executeNonQuery(m_sql, m_paramList);
        m_sql = "";
        m_paramList.Clear();
    }
    #endregion

    public void createTableIfNotExists()
    {
        if (null == m_tableName || null == m_fieldList) {
            return;
        }

        List<string> list = new List<string>();
        for (int i = 0; i < m_fieldList.Length; i++ )
        {
            list.Add(m_fieldList[i] + " " + m_fieldType[i]);
        }

        string sql = "create table if not exists " + m_tableName
            +"("
            + String.Join(",", list.ToArray())
            +")";
        
        this.executeNonQuery(sql);
    }
    
    /// <summary>
    /// Execute the sql without return value request
    /// </summary>
    /// <param name="sql"></param>
    public void executeNonQuery(string sql)
    {
        this.executeNonQuery(sql, null);
    }

    /**
     * @desc execute sql update, insert, delete (non query)
     * @author vietnb
     * @since 2014/04/28
     **/
    public void executeNonQuery(string sql, Hashtable queryParams)
    {
        try
        {
            SqliteAdapter.Instance.Open();
            SQLiteCommand cmd = SqliteAdapter.Instance.CreateCommand();
            cmd = SqliteAdapter.Instance.CreateCommand();
            cmd.CommandText = sql;

            if (queryParams != null)
            {
                foreach (DictionaryEntry entry in queryParams)
                {
                    cmd.Parameters.AddWithValue(entry.Key.ToString(), entry.Value);
                }
            }
            cmd.ExecuteNonQuery();
        }
        catch (SQLiteException e)
        {
            throw e;
        }
        finally
        {
            SqliteAdapter.Instance.Close();
        }
    }

    /**
     * @desc execute sql select (one param)
     * @author vietnb
     * @since 2014/04/28
     **/
    public List<object[]> executeQuery(string sql)
    {
        return this.executeQuery(sql, null);
    }

    public object[] executeSingleRowQuery(string sql, Hashtable queryParams)
    {
        SQLiteDataReader dataReader = null;
        object[] oResult = null;
        int totalField = 0;
        try
        {
            // open connection
            SqliteAdapter.Instance.Open();

            // build sql command
            SQLiteCommand cmd = SqliteAdapter.Instance.CreateCommand();
            cmd.CommandText = sql;
            if (queryParams != null)
            {
                foreach (DictionaryEntry entry in queryParams)
                {
                    cmd.Parameters.AddWithValue(entry.Key.ToString(), entry.Value);
                }
            }

            // get data
            dataReader = cmd.ExecuteReader();
            totalField = dataReader.FieldCount;

            while (dataReader.Read())
            {
                oResult = new object[totalField];
                for (int i = 0; i < totalField; i++)
                {
                    oResult[i] = dataReader[i];
                }
            }
        }
        catch (SQLiteException e)
        {
            throw e;
        }
        finally
        {
            SqliteAdapter.Instance.Close();
        }
        return oResult;
    }

    /**
     * @desc execute sql select (two param) - overload
     * @author vietnb
     * @since 2014/04/28
     **/
    public List<object[]> executeQuery(string sql, Hashtable queryParams)
    {
        SQLiteDataReader dataReader = null;
        List<object[]> results = new List<object[]>();
        int totalField = 0;
        try
        {
            // open connection
            SqliteAdapter.Instance.Open();

            // build sql command
            SQLiteCommand cmd = SqliteAdapter.Instance.CreateCommand();
            cmd.CommandText = sql;
            if (queryParams != null)
            {
                foreach (DictionaryEntry entry in queryParams)
                {
                    cmd.Parameters.AddWithValue(entry.Key.ToString(), entry.Value);
                }
            }

            // get data
            dataReader = cmd.ExecuteReader();
            totalField = dataReader.FieldCount;

            while (dataReader.Read())
            {
                object[] o = new object[totalField];
                for (int i = 0; i < totalField; i++)
                {
                    o[i] = dataReader[i];
                }
                results.Add(o); 
            }
        }
        catch (SQLiteException e)
        {
            throw e;
        }
        finally
        {
            SqliteAdapter.Instance.Close();
        }
        return results;
    }

    /// <summary>
    /// Perform SQL select command
    /// </summary>
    /// <param name="sql">Sql command to excute</param>
    /// <returns>Data table</returns>
    /// <remarks>trungpv@vnext.vn</remarks>
    public DataTable excuteSQLSelect(string sql)
    {
        DataTable dt = null;
        try
        {
            this.conn.Open();
            SQLiteHelper hp = new SQLiteHelper(this.conn.CreateCommand());
            dt = hp.Select(sql);
        }
        catch (SQLiteException ex)
        {
        }
        finally
        {
            this.conn.Close();
        }
        return dt;
    }
}

